package asorm.db
{
	import flash.data.SQLConnection;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	import flash.data.SQLResult;
	import flash.filesystem.File;
	import flash.data.SQLStatement;
	import asorm.db.event.LocalDatabaseEvent;

	public class DAO
	{
		public static var NULL_ID: Number = -1;
		
		public function DAO(table: LocalDatabaseTable) {
			_table = table;
			_columnToFieldMap = table.propertyMapping;		
			_fieldToColumnMap = new Object();
			for (var columnName: String in _columnToFieldMap) {
				_fieldToColumnMap[_columnToFieldMap[columnName]] = columnName;
			}
		}
		
		public function load(): void {
			executeSQL(this.loadQuery);
		}
		
		public function insert(): void {
			executeSQL(this.insertQuery);
		}
		
		public function update(): void {
			executeSQL(this.updateQuery);
		}
		
		public function dispose(): void {
			executeSQL(this.disposeQuery);
		}
		
		public function get tableName(): String {
			return _table.name;
		}
		
		public function get fullTableName(): String {
			return _table.database.name + "." + _table.name;
		}
		
		public function set primaryKeyField(pkField: String): void {
			this._primaryKeyField = pkField;
		}
		
		public function get primaryKeyField(): String {
			return this._primaryKeyField;
		}
		
		public function get primaryKeyValue(): Object {
			return getFieldValue(this._primaryKeyField);
		}
		
		public function get columnNames(): String {
			var list: String = "";
			for (var fieldName:String in _fieldToColumnMap) {
				if (fieldName != _primaryKeyField) {
					list += _fieldToColumnMap[fieldName] + ",";
				}
			}
			// return list without last ','
			return list.substr(0, list.length-1);
		}
		
		public function get insertQuery(): String {
			var sql:String = 
				"INSERT INTO " + this.tableName + 
				"(" + this.columnNames + ")" + 
				" VALUES(" + this.fieldValues + ")";
			return sql;
		}
		
		public function get updateQuery(): String {
			var sql:String = "UPDATE " + this.tableName + " SET ";
			var assignments:String = "";
			for (var columnName:String in _columnToFieldMap) {
				if (_columnToFieldMap[columnName]!=this.primaryKeyField) {
					var value:String = getFieldValueForInsert(_columnToFieldMap[columnName]);
					if (value != null) {
						if (assignments.length > 0) {
							assignments += ", ";
						}
						assignments += columnName + " = " + value;
					}
				}
			}
			sql += assignments + limitQueryToThisEntity();
			return sql;
		}
		
		public function get loadQuery(): String {
			var sql:String = 
				"SELECT " + this.fieldValues + 
				" FROM " + this.tableName + 
				limitQueryToThisEntity();
			return sql;
		}
		
		public function get disposeQuery(): String {
			var sql:String = 
				"DELETE FROM " + this.tableName + 
				limitQueryToThisEntity();
			return sql;
		}
		
		private function limitQueryToThisEntity(): String {
			return " WHERE " + _fieldToColumnMap[_primaryKeyField] + "=" + getFieldValueForInsert(_primaryKeyField);	
		}
		
		private function getFieldValueForInsert(fieldName: String): String {
			var value: Object = this[fieldName];
			if (value is String) {
				return "'" + value.toString() + "'";
			}
			else if (value is Date) {
				var d:Date = value as Date;
				return "date('" + d.getUTCFullYear() + "-" + d.getUTCMonth() + "-" + d.getUTCDay() + "')";
			}
			else {
				return value==null ? null : value.toString();
			}
		}
		
		private function executeSQL(sql:String): void {
			_table.executeSQL(sql, handleSQLExecutedEvent);
		}
		
		public function getFieldValue(fieldName: String): Object {
			var value: Object = this[fieldName];
			if (value == null) {
				return null;
			} else {
				return value;
			}
		}
		
		public function get fieldValues(): String {
			var list: String = "";
			for (var fieldName:String in _fieldToColumnMap) {
				if (fieldName != _primaryKeyField) {
					list += getFieldValueForInsert(fieldName) + ",";
				}
			}
			// return list without last ','
			return list.substr(0, list.length-1);
		}
		
		public function addField(fieldName: String, columnName: String): void {
			_fieldToColumnMap[fieldName] = columnName;
			_columnToFieldMap[columnName] = fieldName;
		}
		
		public function setPropertyValues(values: Object): void {
			for (var columnName:String in values) {
				var fieldName: String = _columnToFieldMap[columnName];
				if (fieldName != null) {
					try {
						this[fieldName] = values[columnName];
					}
					catch (err:Error) {
						trace("Error setting property value: ", err.message);
					}
				}
			}
		}
				
		function handleSQLExecutedEvent(event:LocalDatabaseEvent): void {
			switch (event.queryType) {
				case LocalDatabaseEvent.QUERYTYPE_SELECT:
					handleLoadResult(event.result);
					break;
				case LocalDatabaseEvent.QUERYTYPE_INSERT:
					handleInsertResult(event.sqlStatement.sqlConnection.lastInsertRowID);
					break;
				case LocalDatabaseEvent.QUERYTYPE_UPDATE:
					handleUpdateResult();
					break;
				case LocalDatabaseEvent.QUERYTYPE_DELETE:
					handleDisposeResult();
					break;
				default:
					break;	
			} 
			_table.database.removeEventListener(LocalDatabaseEvent.DATABASE_SQL_EXECUTED, handleSQLExecutedEvent);
			
		}

		function handleLoadResult(result:SQLResult): void {
			trace("'load' query succesfully executed");
			if (result.data.length == 1) {
				setPropertyValues(result.data[0]);
			}
		}
		
		function handleInsertResult(lastInsertRowID:int): void {
			trace("DAO succesfully inserted");
			this[this.primaryKeyField] = lastInsertRowID;
		}
		
		function handleUpdateResult(): void {
			trace("DAO succesfully updated");
		}
		
		function handleDisposeResult(): void {
			trace("DAO succesfully disposed");
		}
		
		private var _table: LocalDatabaseTable;
		private var _fieldToColumnMap: Object;
		private var _columnToFieldMap: Object;
		private var _primaryKeyField: String;
		
	}
}